using System;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Drawing.Imaging;
using System.IO;
namespace SSG {
	public partial class SqlGenerator:Form {
		int limit=Settings.Limit;
		string m_sSqlConnectionString=string.Empty;
		DataTable m_TableInfo=null;
		string m_sSqlStatementText=string.Empty;
		enum STATEMENT_TYPES { INSERT, UPDATE, DELETE }
		public ToolTip tooltip=new ToolTip();
		string tablename;
		public SqlGenerator() {
			InitializeComponent();
			tmr_refresh.Tick+=new EventHandler(tmr_refresh_Tick);
			//tmr_refresh.Interval=500;
		}
		void tmr_refresh_Tick(object sender,EventArgs e) {
			try
			{
				LoadQueryRecords((txtSelectSQL.SelectedText.Length!=0)?txtSelectSQL.SelectedText:txtSelectSQL.Text.Trim());
			}
			catch
			{
				chkRefresh.Checked=false;
				throw;
			}
		}
		void SqlGenerator_Load(object sender,EventArgs e) {
			cmbSqlType.SelectedIndex=(int)STATEMENT_TYPES.INSERT;
			m_sSqlConnectionString=Settings.ActiveConstr;
			if(m_sSqlConnectionString==null)
			{
				btnConfig_Click(null,null);
				return;
			}
			InitializeDatabaseControls();
			this.Text=Settings.ActiveConstrName;
		}
		private void btnShowQueryResults_Click(object sender,System.EventArgs e) {
			LoadQueryRecords(txtSelectSQL.Text.Trim());
		}
		private void btnGenerate_Click(object sender,System.EventArgs e) {
			GenerateSqlStatements();
			// if user cancelled or problem occured, just exit
			if(m_sSqlStatementText.Trim()==string.Empty)
				return;
			string sFilePath=Path.Combine(Path.GetTempPath(),txtTargetTable.Text+DateTime.Now.ToString("_yyMMddHHmmss")+".sql");
			File.WriteAllText(sFilePath,m_sSqlStatementText);
			Process.Start(sFilePath);
		}
		object sync=new object();
		private void cmbDatabases_SelectedIndexChanged(object sender,EventArgs e) {
			if(cmbDatabases.SelectedIndex<0)
				return;
			cmbTables.DataSource=null;
			cmbTables.Items.Clear();
			cmbTables.DisplayMember="table_name";
			cmbTables.ValueMember="val";
			DataTable dt=DatabaseUtilities.GetTables(m_sSqlConnectionString,cmbDatabases.Text);
			cmbTables.Enabled=dt.Rows.Count>0;
			if(dt.Rows.Count>0)
			{
				cmbTables.DataSource=dt;
			}
		}
		private void cmbTables_SelectedIndexChanged(object sender,EventArgs e) {
			if(cmbTables.SelectedIndex<0)
				return;
			string tableName;
			string[] sTableNameParts=cmbTables.SelectedValue.ToString().Split(new char[] { '.' },2);
			sTableNameParts[1]=Utils.EscapeKeywords(sTableNameParts[1]);
			if(sTableNameParts[0].ToLower()=="dbo")
				tableName=sTableNameParts[1];
			else
				tableName=string.Join(".",sTableNameParts);
			txtSelectSQL.Text=string.Format("SELECT top {0} * FROM {1}(nolock)",limit,tableName);
			txtTargetTable.Text=sTableNameParts[1];
			dgTableInfo.DataSource=null;
			UpdateControls();
			LoadQueryRecords(txtSelectSQL.Text.Trim());


		}
		private void cmbSqlType_SelectedIndexChanged(object sender,EventArgs e) {
			if(cmbTables.SelectedIndex<0)
				return;
			// user changed the type of statement to generate, 
			//  so flip the checkboxes for user convenience
			for(int i=0;i<chklstIncludeFields.Items.Count;i++)
				chklstIncludeFields.SetItemChecked(i,true);
		}
		private void btnClose_Click(object sender,EventArgs e) {
			Application.Exit();
		}
		/// <summary>
		/// Initializes the database combobox control
		/// </summary>
		private void InitializeDatabaseControls() {
			cmbDatabases.DataSource=null;
			cmbDatabases.Items.Clear();
			cmbTables.DataSource=null;
			cmbTables.Items.Clear();     // clear the tables also since they'll be invalid       
			cmbDatabases.DisplayMember="name";
			cmbDatabases.ValueMember="name";
			DataTable who=DatabaseUtilities.LoadDataTable(m_sSqlConnectionString,null,"sp_who2");
			foreach(DataRow row in who.Rows)
				if((row["ProgramName"].ToString().ToLower().Contains("profiler")))
				{
					who.TableName="sp_who2";
					who.WriteXml("prof"+DateTime.Now.ToString("yyMMdd_HHmm")+".xml");
					DialogResult dres=MessageBox.Show(string.Join("\n",new string[] { row["ProgramName"].ToString(),row["HostName"].ToString(),row["Login"].ToString(),row["DBName"].ToString(),"Do you want to continue?" }),Settings.ActiveConstrName+" - Profiler",MessageBoxButtons.YesNo);
					if(dres==DialogResult.No)
					{
						btnConfig_Click("init",null);
						return;
					}
				}
			DataTable dt=DatabaseUtilities.GetDatabases(m_sSqlConnectionString);
			cmbDatabases.DataSource=dt;
			//cmbDatabases.Items.Clear();
			//foreach(DataRow row in dt.Rows)
			//{
			//  cmbDatabases.Items.Add(row[0].ToString());
			//}
		}
		/// <summary>
		/// Updates the enable states of various form controls
		/// </summary>
		private void UpdateControls() {
			btnShowQueryResults.Enabled=(cmbDatabases.SelectedIndex>=0);
			btnGenerate.Enabled=(dgTableInfo.DataSource!=null);
		}
		/// <summary>
		/// Loads the grid and column list controls according to the specified query
		/// </summary>
		/// <param name="sSQL"></param>
		private void LoadQueryRecords(string sSQL) {
			if(sSQL.Trim()==string.Empty)
			{
				MessageBox.Show("The SQL query was empty! Please enter a valid SQL query!");
				return;
			}
			Stopwatch sw=Stopwatch.StartNew();
			m_TableInfo=DatabaseUtilities.LoadDataTable(m_sSqlConnectionString,cmbDatabases.Text,sSQL);

			//Application.DoEvents();
			sw.Stop();
			SetTooltipInfo();
			if(m_TableInfo!=null)
			{
				dgTableInfo.DataSource=m_TableInfo;
				//m_TableInfo.WriteXmlSchema("c:\\ElmDrIpsSchema.xsd");
				//m_TableInfo.WriteXml("c:\\ElmDrIpsData.xml");
				lblResults.Text=m_TableInfo.Rows.Count+" rows";
				lblResults.Text+=" ("+sw.Elapsed.TotalSeconds+")";
				// load the list of columns to include in the generator
				chklstIncludeFields.Items.Clear();
				if(genVisible)
				{
					int start=sSQL.IndexOf("from",StringComparison.CurrentCultureIgnoreCase)+4;
					string tab=sSQL.Substring(start).Trim();
					int cnt=0;
					foreach(char ch in tab)
					{
						if(char.IsLetter(ch))
							cnt++;
						else
							break;
					}
					tab=tab.Substring(0,cnt);
					txtTargetTable.Text=tab;
					foreach(DataColumn col in m_TableInfo.Columns)
					{
						// exclude the primary/auto-increment key by default, but select/check all the others
						chklstIncludeFields.Items.Add(col.ColumnName,true /*(chklstIncludeFields.Items.Count>0)*/);
					}
				}
			}
			UpdateControls();
		}
		private void GenerateSqlStatements() {
			if(chklstIncludeFields.Items.Count==0)
				foreach(DataColumn col in m_TableInfo.Columns)
				{
					// exclude the primary/auto-increment key by default, but select/check all the others
					chklstIncludeFields.Items.Add(col.ColumnName,true /*(chklstIncludeFields.Items.Count>0)*/);
				}
			// clear the string member
			m_sSqlStatementText=string.Empty;
			// create an array of all the columns that are to be included
			ArrayList aryColumns=new ArrayList();
			for(int i=0;i<chklstIncludeFields.CheckedItems.Count;i++)
			{
				aryColumns.Add(chklstIncludeFields.CheckedItems[i].ToString());
			}
			// if no columns included, return with a msg
			if(aryColumns.Count<=0)
			{
				MessageBox.Show("No columns selected!  Please check/select some columns to include!");
				return;
			}
			string sTargetTableName=txtTargetTable.Text.Trim();
			if(sTargetTableName==string.Empty)
			{
				MessageBox.Show("No valid target table name!  Please enter a table name to be used in the SQL statements!");
				return;
			}
			// generate the sql by type
			if(cmbSqlType.SelectedIndex==(int)STATEMENT_TYPES.INSERT)
			{
				string identCol=DatabaseUtilities.GetIdentityColumnName(m_sSqlConnectionString+"database="+cmbDatabases.Text,sTargetTableName);
				m_sSqlStatementText=SqlScriptGenerator.GenerateSqlInserts(aryColumns,m_TableInfo,sTargetTableName,identCol);
			}
			else if(cmbSqlType.SelectedIndex==(int)STATEMENT_TYPES.UPDATE)
			{
				// get an array of all the active table columns         
				ArrayList aryWhereColumns=new ArrayList();
				for(int i=0;i<chklstIncludeFields.Items.Count;i++)
				{
					aryWhereColumns.Add(chklstIncludeFields.GetItemText(chklstIncludeFields.Items[i]));
				}
				// create dlg and pass in array of columns
				SelectMultipleItems dlg=new SelectMultipleItems();
				dlg.Text="Select WHERE Columns";
				dlg.Description="Select WHERE-Clause Columns for UPDATE SQLs:";
				dlg.Initialize(aryWhereColumns,string.Empty,false);
				// user cancelled, so exit
				if(dlg.ShowDialog()!=DialogResult.OK)
				{
					return;
				}
				aryWhereColumns.Clear();
				aryWhereColumns=dlg.UserSelectedItems;
				m_sSqlStatementText=SqlScriptGenerator.GenerateSqlUpdates(aryColumns,aryWhereColumns,m_TableInfo,sTargetTableName);
			}
			else if(cmbSqlType.SelectedIndex==(int)STATEMENT_TYPES.DELETE)
			{
				m_sSqlStatementText=SqlScriptGenerator.GenerateSqlDeletes(aryColumns,m_TableInfo,sTargetTableName);
			}
		}
		private void btnTest_Click(object sender,EventArgs e) {
			//m_TableInfo=new DataTable();
			string ext="Dat files (*.dat)|*.dat|All files (*.*)|*.*";
			if(!ctrlPressed)
				using(FileDialog dlg=new SaveFileDialog())
				{
					dlg.Filter=ext;
					if(dlg.ShowDialog()==DialogResult.OK)
						Storage.Save(dlg.FileName,m_TableInfo);
				}
			else
				using(FileDialog dlg=new OpenFileDialog())
				{
					dlg.Filter=ext;
					if(dlg.ShowDialog()==DialogResult.OK)
					{
						dgTableInfo.DataSource=Storage.Load(dlg.FileName);
					}
				}
			ctrlPressed=false;
		}
		private void btnConfig_Click(object sender,EventArgs e) {
			using(Form fs=new Form())
			{
				fs.Text="Settings";
				fs.StartPosition=FormStartPosition.CenterParent;
				fs.FormBorderStyle=FormBorderStyle.FixedToolWindow;
				//radios
				string[] allCsNames=Settings.AllCsNames;
				string current=Settings.ActiveConstrName;
				Array.Sort(allCsNames);
				int hspacing=20;
				int top=10;
				int cnt=0;
				foreach(string str in allCsNames)
					if(!str.StartsWith("-"))
					{
						RadioButton rb=new RadioButton();
						//rb.BackColor=Color.Yellow;
						rb.AutoSize=true;
						rb.Text=str;
						if(str.Contains("wpsq"))
							rb.ForeColor=Color.DarkRed;
						if(current==str)
							rb.Checked=true;
						rb.Left=10;
						rb.Top=cnt*hspacing+top;
						Debug.Assert(rb.Parent==null);
						fs.Controls.Add(rb);
						Debug.Assert(rb.Parent==fs);
						cnt++;
					}
				fs.Height=cnt*hspacing+60;
				//button
				Button btnOk=new Button();
				btnOk.Text="OK";
				btnOk.DialogResult=DialogResult.OK;
				btnOk.Left=fs.Width-btnOk.Width-20;
				btnOk.Top=fs.Height-btnOk.Height-30;
				fs.Controls.Add(btnOk);
				//show the form
				DialogResult res=fs.ShowDialog();
				if(res==DialogResult.OK)
				{
					foreach(Control ctrl in fs.Controls)
						if(ctrl is RadioButton)
						{
							RadioButton rb=ctrl as RadioButton;
							if(rb.Checked)
							{
								Settings.ActiveConstrName=rb.Text;
								SqlGenerator_Load(null,null);
								break;
							}
						}
				}
				else if(res==DialogResult.Cancel&&"init".Equals(sender))
				{
					Application.Exit();
				}
			}
		}
		private void lblResults_DoubleClick(object sender,EventArgs e) {
			if(((DataTable)dgTableInfo.DataSource).Rows.Count>=limit)
			{
				lblResults.Text="Wait...";
				Application.DoEvents();
				string query=string.Format("select rowcnt from sysindexes(nolock) where object_name(id)='{0}' and indid in (0,1)",cmbTables.Text);
				Stopwatch sw=Stopwatch.StartNew();
				object obj=DatabaseUtilities.LoadDataTable(m_sSqlConnectionString,cmbDatabases.Text,query).Rows[0][0];
				if(obj.GetType()==typeof(byte[]))
				{
					string query2=string.Format("select count(*) from {0}(nolock)",Utils.EscapeKeywords(cmbTables.Text));
					obj=DatabaseUtilities.LoadDataTable(m_sSqlConnectionString,cmbDatabases.Text,query2).Rows[0][0];
					//obj=Encoding.Unicode.GetString(obj as byte[]);
				}
				lblResults.Text=string.Format("Total rows:{0:### ### ###}, Time:{1}",obj,sw.Elapsed.TotalSeconds);
			}
		}
		private void dgTableInfo_CellMouseDown(object sender,DataGridViewCellMouseEventArgs e) {
			if(e.Button==MouseButtons.Right)
			{
				if(e.ColumnIndex>=0)
					if(e.RowIndex>=0)
					{
						object ob=dgTableInfo[e.ColumnIndex,e.RowIndex].Value;
						if(ob is byte[])
						{
							using(SaveFileDialog sd=new SaveFileDialog())
								if(sd.ShowDialog()==DialogResult.OK)
								{
									using(FileStream fs=new FileStream(sd.FileName,FileMode.Create,FileAccess.ReadWrite))
									using(BinaryWriter bw=new BinaryWriter(fs))
									{
										bw.Write(ob as byte[]);
										bw.Close();
									}
								}
						}
						else
							txtSelectSQL.SelectedText=ob.ToString().Trim();
					}
					else
					{
						if(!ctrlPressed)
							txtSelectSQL.SelectedText=dgTableInfo.Columns[e.ColumnIndex].HeaderText;
						else
						{
							string colSel=dgTableInfo.Columns[e.ColumnIndex].HeaderText;
							if(dgTableInfo.Columns[e.ColumnIndex].ValueType==typeof(DateTime))
								txtSelectSQL.Text=string.Format("select convert(char(10),{0},120)[day],count(*)[count] from {1}(nolock) group by convert(char(10),{0},120)order by 1 desc",colSel,Utils.EscapeKeywords(cmbTables.Text));
							else
								txtSelectSQL.Text=string.Format("select {0},count(*)[count] from {1}(nolock) group by {0}",colSel,Utils.EscapeKeywords(cmbTables.Text));
						}
					}
			}
		}
		private void lblPicture_Click(object sender,EventArgs e) {
			using(SaveFileDialog sav=new SaveFileDialog())
			{
				sav.DefaultExt="png";
				sav.Filter="PNG files (*.png)|*.png|All files (*.*)|*.*";
				sav.AddExtension=true;
				sav.FileName=cmbDatabases.Text+"."+cmbTables.Text;
				if(sav.ShowDialog()==DialogResult.OK)
				{
					Control ctrl=dgTableInfo;
					RenderControl(ctrl,sav.FileName);
				}
			}
		}
		private void RenderControl(Control ctrl,string FileName) {
			using(Bitmap bmp=new Bitmap(ctrl.Width,dgTableInfo.Height))
			{
				dgTableInfo.DrawToBitmap(bmp,ctrl.ClientRectangle);
				using(Image img=Image.FromHbitmap(bmp.GetHbitmap()))
					img.Save(FileName,ImageFormat.Png);
				Process.Start(FileName);
			}
		}
		private void dgTableInfo_DataError(object sender,DataGridViewDataErrorEventArgs e) {
			e.Cancel=true;
			e.ThrowException=false;
		}
		private void dgTableInfo_CellParsing(object sender,DataGridViewCellParsingEventArgs e) {
		}
		private void btnWidth_Click(object sender,EventArgs e) {
			int ias=(int)dgTableInfo.AutoSizeColumnsMode;
			switch(ias)
			{
			case 1:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)2;
			break;
			case 2:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)4;
			break;
			case 4:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)6;
			break;
			case 6:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)8;
			break;
			case 8:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)10;
			break;
			case 10:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)16;
			break;
			default:
			dgTableInfo.AutoSizeColumnsMode=(DataGridViewAutoSizeColumnsMode)1;
			break;
			}
		}
		private void cmbTables_MouseDown(object sender,MouseEventArgs e) {
			if(e.Button==MouseButtons.Right)
				if(sender is ComboBox)
				{
					ComboBox cmb=sender as ComboBox;
					txtSelectSQL.SelectedText=Utils.EscapeKeywords(cmb.Text);
				}
		}
		bool ctrlPressed=false;
		private void txtSelectSQL_KeyDown(object sender,KeyEventArgs e) {
			if(e.KeyCode==Keys.F5||(e.Alt&e.KeyCode==Keys.X))//alt+X
			{//F5
				LoadQueryRecords((txtSelectSQL.SelectedText.Length!=0)?txtSelectSQL.SelectedText:txtSelectSQL.Text.Trim());
			}
			else if(e.KeyCode==(Keys.LButton|Keys.ShiftKey))
			{
				Icon=new Icon(typeof(SqlGenerator).Assembly.GetManifestResourceStream("SSG.Img.tool2.ico"));
				ctrlPressed=true;
			}
		}
		private void txtSelectSQL_KeyUp(object sender,KeyEventArgs e) {
			if(e.KeyCode==(Keys.LButton|Keys.ShiftKey))
			{
				ctrlPressed=false;
				Icon=new Icon(typeof(SqlGenerator).Assembly.GetManifestResourceStream("SSG.Img.tool1.ico"));
			}
		}
		bool genVisible=false;
		private void btnGenerate_MouseUp(object sender,MouseEventArgs e) {
			if(e.Button==MouseButtons.Right)
			{
				dgTableInfo.Width+=(genVisible)?135:-135;
				genVisible^=true;
			}
		}
		private void cmbDatabases_MouseDown(object sender,MouseEventArgs e) {
			if(e.Button==MouseButtons.Right)
			{
				Stopwatch sw=Stopwatch.StartNew();
				DataTable tab=new DataTable();
				string sql=@"create Table #temp(table_name sysname,row_count int,
reserved_size varchar(30),data_size varchar(30),
index_size varchar(30),unused_size varchar(30))
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select * from #temp order by 2 desc
drop table #temp";
				m_TableInfo=DatabaseUtilities.LoadDataTable(m_sSqlConnectionString,cmbDatabases.Text,sql);
				sw.Stop();
				Application.DoEvents();
				if(m_TableInfo!=null)
				{
					dgTableInfo.DataSource=m_TableInfo;
					lblResults.Text=sw.Elapsed.TotalSeconds.ToString();
				}
			}
		}
		private void SetTooltipInfo() {
			this.tooltip.SetToolTip(btnShowQueryResults,DatabaseUtilities.StatsStr);
		}
		private void btnShowQueryResults_MouseUp(object sender,MouseEventArgs e) {
			if(e.Button==MouseButtons.Right)
				if(dgTableInfo.Columns.Count==2)
					if(dgTableInfo.Columns[1].Name=="count")
						if(dgTableInfo.Rows.Count>0)
						{
							int max=0;
							foreach(DataGridViewRow row in dgTableInfo.Rows)
							{
								int val=int.Parse(row.Cells[1].Value.ToString());
								if(val>max)
									max=val;
								row.Tag=val;
							}
							int Height=800;
							int wid=11;
							string filename="test.png";
							Font fnt=new Font("arial",8);
							using(Bitmap bmp=new Bitmap(dgTableInfo.Rows.Count*wid+50,Height+50))
							using(Graphics g=Graphics.FromImage(bmp))
							{
								g.FillRectangle(Brushes.White,1,1,bmp.Width-2,bmp.Height-2);
								//g.SmoothingMode=System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
								foreach(DataGridViewRow row in dgTableInfo.Rows)
								{
									g.DrawRectangle(new Pen(Color.FromArgb(15,Color.Black)),row.Index*wid,0,wid,bmp.Height);
									float thigh=Height*(float)int.Parse(row.Tag.ToString())/max;
									g.FillRectangle(Brushes.YellowGreen,row.Index*wid,bmp.Height-thigh,wid,thigh);
									g.DrawRectangle(Pens.Black,row.Index*wid,bmp.Height-thigh,wid,thigh);
									string str=row.Cells[0].Value.ToString()+": "+row.Cells[1].Value.ToString();
									SizeF siz=g.MeasureString(str,fnt);
									//g.FillRectangle(new SolidBrush(Color.FromArgb(60,Color.LightYellow)),row.Index*wid-2,bmp.Height-siz.Width,siz.Height,siz.Width);
									g.DrawString(str,fnt,Brushes.Black,row.Index*wid-2,bmp.Height-siz.Width,new StringFormat(StringFormatFlags.DirectionVertical));
								}
								bmp.Save(filename,ImageFormat.Png);
							}
							Process.Start(filename);
						}
		}
		Timer tmr_refresh=new Timer();
		private void chkRefresh_CheckedChanged(object sender,EventArgs e) {
			tmr_refresh.Enabled=chkRefresh.Checked;
			this.TopMost=chkRefresh.Checked;
		}
	}
}